package de.schlinke_marius.database;



import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class MSCDatabase extends SQLiteOpenHelper implements ExamplesTbl, BenchmarkTbl, HardwareTbl, HardwaretoExamplesTbl, GalleryPicturesTbl, QuestionsTbl, AnswersTbl, DatabaseConstrants {
	
    private Cursor cursor;
	
	private long ret;
	
	private SQLiteStatement sqlStatement;

	public MSCDatabase(Context context) {
		super(context, 
				path, 
				null, 
				version);
	
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		db.execSQL(CREATE_TABLE_EXAMPLES);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_EXAMPLES);
		db.execSQL(CREATE_TABLE_BENCHMARK);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_BENCHMARK);
		db.execSQL(CREATE_TABLE_HARDWARE);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_HARDWARE);
		db.execSQL(CREATE_TABLE_HARDWARETOEXAMPLES);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_HARDWARETOEXAMPLES);
		db.execSQL(CREATE_TABLE_GALLERYPICTURES);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_GALLERYPICTURES);
		db.execSQL(CREATE_TABLE_QUESTIONS);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_QUESTIONS);
		db.execSQL(CREATE_TABLE_ANSWERS);
		Log.i("CREATE TABLE" , "SQL: " + CREATE_TABLE_ANSWERS);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

	@Override
	public long insertIntoExamples(int id, String name, String date,
			String price, String pictureurl, String description, String comment, String hardwarecosts, String softwarecosts, String consultingcosts, String extrascosts, String benchmark, String benchmarkcpu, String benchmarkgraphic) throws SQLiteException {
		return insertIntoExamples(getWritableDatabase(), id, name, date, price, pictureurl, description, comment, hardwarecosts, softwarecosts, consultingcosts, extrascosts, benchmark, benchmarkcpu, benchmarkgraphic);
	}
	private long insertIntoExamples(SQLiteDatabase db, int id, String name, String date, String price, String pictureurl, String description, String comment, String hardwarecosts, String softwarecosts, String consultingcosts, String extrascosts, String benchmark, String benchmarkcpu, String benchmarkgraphic) throws SQLiteException{
		sqlStatement = db.compileStatement(INSERT_INTO_EXAMPLES);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindString(2, name);
		sqlStatement.bindString(3, date);
		sqlStatement.bindString(4, price);
		sqlStatement.bindString(5, pictureurl);
		sqlStatement.bindString(6, description);
		sqlStatement.bindString(7, comment);
		sqlStatement.bindString(8, hardwarecosts);
		sqlStatement.bindString(9, softwarecosts);
		sqlStatement.bindString(10, consultingcosts);
		sqlStatement.bindString(11, extrascosts);
		sqlStatement.bindString(12, benchmark);
		sqlStatement.bindString(13, benchmarkcpu);
		sqlStatement.bindString(14, benchmarkgraphic);
		ret = sqlStatement.executeInsert();			
		db.close();
		return ret;
	}

	@Override
	public Cursor getExamples() throws SQLiteException {
		return getExamples(getReadableDatabase());
	}
	private Cursor getExamples(SQLiteDatabase db)  throws SQLiteException {
		return cursor = db.query(
				ExamplesTbl.TABLE_NAME,
				ExamplesTbl.COLUMNS, 
				null,
				null,
				null,
				null,
				COLUMN_PRICE + " ASC");	
	}
	
	@Override
	public Cursor getExample(int id) throws SQLiteException {
		return getExample(getReadableDatabase(), id);
	}
	private Cursor getExample(SQLiteDatabase db, int id) throws SQLiteException {
		return cursor = db.query(
				ExamplesTbl.TABLE_NAME, 
				ExamplesTbl.COLUMNS, 
				ExamplesTbl.COLUMN_ID + "=?", 
				new String[] { String.valueOf(id)}, 
				null, 
				null, 
				null);
		
	}

	@Override
	public long insertIntoBenchmark(int id, int fkExamples,
			String benchmarkName, String benchmarkType, String benchmarkValue) throws SQLiteException {
		return insertIntoBechmark(getWritableDatabase(), id, fkExamples, benchmarkName, benchmarkType, benchmarkValue);
	}
    private long insertIntoBechmark(SQLiteDatabase db, int id, int fkExamples, String benchmarkName, String benchmarkType, String benchmarkValue) throws SQLiteException {
		sqlStatement = db.compileStatement(INSERT_INTO_BENCHMARK);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindLong(2, fkExamples);
		sqlStatement.bindString(3, benchmarkName);
		sqlStatement.bindString(4, benchmarkType);
		sqlStatement.bindString(5, benchmarkValue);		
		ret = sqlStatement.executeInsert();				
		db.close();
		return ret;
	}

	@Override
	public Cursor getBenchmark(int id) throws SQLiteException {
		return getBenchmark(getReadableDatabase(), id);
	}
    private Cursor getBenchmark(SQLiteDatabase db, int id) throws SQLiteException {
		return cursor = db.query(
				BenchmarkTbl.TABLE_NAME,
				BenchmarkTbl.COLUMNS, 
				BenchmarkTbl.COLUMN_FK_EXAMPLES + "=?",
				new String[] {String.valueOf(id)},
				null,
				null,
				null);	
	}

	@Override
	public long insertIntoHardware(int id, String hardwareName, String hardwareType) throws SQLiteException {
		return insertIntoHardware(getWritableDatabase(), id, hardwareName, hardwareType);
	}
	private long insertIntoHardware(SQLiteDatabase db, int id, String hardwareName, String hardwareType) throws SQLiteException {
		sqlStatement= db.compileStatement(INSERT_INTO_HARDWARE);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindString(2, hardwareName);
		sqlStatement.bindString(3, hardwareType);	
		ret = sqlStatement.executeInsert();
		db.close();
		return ret;
	}

	@Override
	public Cursor getHardware(int id) throws SQLiteException {
		return getHardware(getReadableDatabase(), id);
	}
	private Cursor getHardware(SQLiteDatabase db, int id) throws SQLiteException {
		return cursor = db.query(
				HardwareTbl.TABLE_NAME,
				HardwareTbl.COLUMNS, 
				HardwareTbl.COLUMN_ID + "=?",
				new String[] { String.valueOf(id)},
				null,
				null,
				null);		
	}

	@Override
	public long insertIntoHardwareToExamples(int fkExamples, int fkHardware)
			throws SQLiteException {
		return insertIntoHardwareToExamples(getWritableDatabase(), fkExamples, fkHardware);
	} 
	private long insertIntoHardwareToExamples(SQLiteDatabase db, int fkExamples, int fkHardware) throws SQLiteException {
		sqlStatement = db.compileStatement(INSERT_INTO_HARDWARETOEXAPLES);
		sqlStatement.bindLong(1, fkExamples);
		sqlStatement.bindLong(2, fkHardware);
		ret = sqlStatement.executeInsert();
		db.close();
		return ret;
	}

	@Override
	public Cursor getHardwareToExamples(int id) throws SQLiteException {

		return getHardwareToExamples(getReadableDatabase(), id);
	}
	private Cursor getHardwareToExamples(SQLiteDatabase db, int id) throws SQLiteException {
		return cursor = db.query(
				HardwaretoExamplesTbl.TABLE_NAME, 
				HardwaretoExamplesTbl.COLUMNS, 
				HardwaretoExamplesTbl.COLUMN_FK_EXAMPLES + "=?", 
				new String[] { String.valueOf(id)},
				null,
				null, 
				null);
	}

	@Override
	public Cursor getGalleryPictures(int id) throws SQLiteException {
		return getGalleryPictures(getReadableDatabase(), id);
	}
	private Cursor getGalleryPictures(SQLiteDatabase db, int id) throws SQLiteException {
		return cursor = db.query(	GalleryPicturesTbl.TABLE_NAME, 
				GalleryPicturesTbl.COLUMNS, 
				GalleryPicturesTbl.COLUMN_FK_EXAMPLES + "=?", 
				new String[] { String.valueOf(id)},
				null,
				null, 
				null);
	}

	@Override
	public long insertIntoGalleryPictures(int id, int fkExamples,
			String pictureurl, String description) throws SQLiteException {
		return insertInotGalleryPictures(getWritableDatabase(), id, fkExamples, pictureurl, description);
	}
	private long insertInotGalleryPictures(SQLiteDatabase db, int id, int fkExamples, String pictureurl, String description) throws SQLiteException {
		
		sqlStatement = db.compileStatement(INSERT_INTO_GALLERYPICTURES);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindLong(2, fkExamples);
		sqlStatement.bindString(3, pictureurl);
		sqlStatement.bindString(4, description);
		ret = sqlStatement.executeInsert();
		db.close();
		return ret;
	}

	@Override
	public long insertIntoQuestions(int id, String question) throws SQLiteException {
		insertIntoQuestions(getWritableDatabase(), id, question);
		return 0;
	}
	
	private long insertIntoQuestions(SQLiteDatabase db, int id, String question) throws SQLiteException {
		sqlStatement = db.compileStatement(INSERT_INTO_QUESTIONS);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindString(2, question);
		ret = sqlStatement.executeInsert();
		db.close();
		return ret;
	}

	@Override
	public Cursor getQuestions(int index) throws SQLiteException {
		return getQuestions(getReadableDatabase(), index);
	}
	
	private Cursor getQuestions(SQLiteDatabase db, int index) throws SQLiteException {
		return cursor = db.query(QuestionsTbl.TABLE_NAME, 
				QuestionsTbl.COLUMNS, 
				GalleryPicturesTbl.COLUMN_ID + "=?", 
				new String[] {String.valueOf(index)}, 
				null, 
				null, 
				null);
	}
	
	@Override
	public Cursor getQuestionCount() throws SQLiteException {
		return getQuestionCount(getReadableDatabase());
	}
	
	private Cursor getQuestionCount(SQLiteDatabase db) throws SQLiteException {
		return cursor = db.rawQuery(QuestionsTbl.COUNT_QUESTIONS, null);
	}

	@Override
	public long insertIntoAnswers(int id, int fkQuestion, String answer)
			throws SQLiteException {
		return insertIntoAnswers(getWritableDatabase(), id, fkQuestion, answer);
	}
	private long insertIntoAnswers(SQLiteDatabase db, int id, int fkQuestion, String answer) throws SQLiteException {
		sqlStatement = db.compileStatement(INSERT_INTO_ANSWERS);
		sqlStatement.bindLong(1, id);
		sqlStatement.bindLong(2, fkQuestion);
		sqlStatement.bindString(3, answer);
		ret = sqlStatement.executeInsert();
		db.close();
		return ret;
	}

	@Override
	public Cursor getAnswers(int id) throws SQLiteException {
		return getAnswer(getReadableDatabase(), id);
	}
	private Cursor getAnswer(SQLiteDatabase db, int id) throws SQLiteException{
		
		return cursor = db.query(AnswersTbl.TABLE_NAME, 
				AnswersTbl.COLUMNS, 
				AnswersTbl.COLUMN_FK_QUESTIONS + "=?", 
				new String [] {String.valueOf(id)}, 
				null, 
				null, 
				null);
	}




	

}
